package com.yongcheon.sms_manager.data.preference;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.yongcheon.sms_manager.utils.StringUtil;
import com.yongcheon.sms_manager.vo.SpamSentenceVO;

import java.util.ArrayList;

/**
 * Created by yongcheon on 2014-12-09.
 */
public class SpamSentenceData {
    Context context;

    public SpamSentenceData(Context context){
        this.context = context;
    }

    public ArrayList<SpamSentenceVO> selectReturnVo(String sentence, Context context, SQLiteDatabase database){
        Cursor cursor = select(sentence, context, database);
        return getSpamSentenceVO(cursor);
    }

    public Cursor select(String sentence, Context context, SQLiteDatabase database){
        String sql = "Select * from spam_sentence";
        if(sentence != null) sql = sql + " Where sentence = '" + sentence+"'";
        sql = sql + " order by sentence asc";
        Cursor cursor = database.rawQuery(sql, null);
        return cursor;
    }

    public ArrayList<SpamSentenceVO> getSpamSentenceVO(Cursor cursor){
        ArrayList<SpamSentenceVO> spamVoList = new ArrayList<SpamSentenceVO>();
        if(cursor != null) {
            while (cursor.moveToNext()) {
                SpamSentenceVO spamVo = new SpamSentenceVO();
                spamVo.setSentence(cursor.getString(cursor.getColumnIndex("sentence")));
                spamVo.setCategory_1(cursor.getString(cursor.getColumnIndex("category_1")));
                spamVo.setCategory_2(cursor.getString(cursor.getColumnIndex("category_2")));
                spamVo.setCategory_3(cursor.getString(cursor.getColumnIndex("category_3")));
                spamVo.setSubject(cursor.getString(cursor.getColumnIndex("subject")));
                spamVo.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
                spamVo.setEtc(cursor.getString(cursor.getColumnIndex("etc")));
                spamVoList.add(spamVo);
            }
            cursor.close();
        }
        return spamVoList;
    }

    public SpamSentenceVO createSpamSentenceVOFromCursor(Cursor cursor){
        SpamSentenceVO spamVo = new SpamSentenceVO();
        spamVo.set_id(cursor.getLong(cursor.getColumnIndex("_id")));
        spamVo.setSentence(cursor.getString(cursor.getColumnIndex("sentence")));
        spamVo.setCategory_1(cursor.getString(cursor.getColumnIndex("category_1")));
        spamVo.setCategory_2(cursor.getString(cursor.getColumnIndex("category_2")));
        spamVo.setCategory_3(cursor.getString(cursor.getColumnIndex("category_3")));
        spamVo.setSubject(cursor.getString(cursor.getColumnIndex("subject")));
        spamVo.setDesc(cursor.getString(cursor.getColumnIndex("desc")));
        spamVo.setEtc(cursor.getString(cursor.getColumnIndex("etc")));
        return spamVo;
    }

    public void insertNotExist(SpamSentenceVO spamVo, Context context, SQLiteDatabase database){
        ArrayList<SpamSentenceVO> spamNumVoList = selectReturnVo(spamVo.getSentence(), context, database);
        if(spamNumVoList.size() == 0){
            insert(spamVo, context, database);
        }
    }


    public void insert(SpamSentenceVO vo, Context context, SQLiteDatabase database){
        String sql = "Insert into spam_sentence (sentence, category_1, category_2, category_3, subject, desc, etc) values "+
                "('"+vo.getSentence()+"','"+vo.getCategory_1()+"','"+vo.getCategory_2()+"','"+vo.getCategory_3()+"','"+ StringUtil.changeSpecialLettersForSQL(vo.getSubject())+"','"+StringUtil.changeSpecialLettersForSQL(vo.getDesc())+"','"+vo.getEtc()+"')";
        database.execSQL(sql);
    }

    public void delete(SpamSentenceVO vo, Context context, SQLiteDatabase database){
        String sql = "Delete From spam_sentence Where _id = '"+vo.get_id()+"'";
        database.execSQL(sql);
    }

    public void update(SpamSentenceVO vo, Context context, SQLiteDatabase database){
        String sql = "Update spam_sentence set sentence = '"+vo.getSentence()+"'  Where _id = '"+vo.get_id()+"'";
        database.execSQL(sql);
    }
}
